package root.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import root.dao.LybDataDao;
import root.entity.po.LiuYan1;
import root.entity.vo.PageInfo;

public class LybDataDaoimpl implements LybDataDao {
	Connection conn = null;
	
	static {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (Exception e) {
			// TODO: handle exception
		}
	}


	
	@Override
	public void flushData(String name, String value, String type, String target, PageInfo page) {
//		获取数据链接
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@120.77.168.195:1521:orcl", "demo", "demo");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
//		组装sql
		String sql="select * from liuyan1 where del = 'false'";
		if(name!=null&&!name.equals("")) {sql += " and name = ?";}
		if(value!=null&&!value.equals("")) {sql += " and value = ?";}
		if(type!=null&&!type.equals("")) {type += " and type = ?";}
		if(target!=null&&!target.equals("")) {sql += " and target = ?";}
		sql +=" order by id desc";
		sql +=" offset "+(page.getFenyepagesize()*(page.getFenyepageindex()-1))
				+" rows fetch next "+page.getFenyepagesize()+" rows only";
		System.out.println(sql);
		PreparedStatement pstm = null;
		ResultSet rs = null;
//		创建执行器 注入参数
		try {
			pstm = conn.prepareStatement(sql);
			int count = 1;
			if(name!=null&&!name.equals("")) {pstm.setString(count++, name);}
			if(value!=null&&!value.equals("")) {pstm.setString(count++, value);}
			if(type!=null&&!type.equals("")) {pstm.setString(count++, type);}
			if(target!=null&&!target.equals("")) {pstm.setString(count++, target);}
//			获得查询结果
			rs = pstm.executeQuery();
//			封装结果
			List<LiuYan1> list = new ArrayList<LiuYan1>();
			page.setList(list);
			while(rs.next()) {
				LiuYan1 ly = new LiuYan1();
				ly.setDatetime(new Date(rs.getTimestamp("datetime").getTime()));
				ly.setId(rs.getInt("id"));
				ly.setName(rs.getString("name"));
				ly.setTarget(rs.getString("target"));
				ly.setType(rs.getString("type"));
				ly.setValue(rs.getString("value"));
				list.add(ly);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}



	@Override
	public boolean sendData(String name, String value, String type, String target, String ip) {

//		获取数据链接
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "lhh", "lhh");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
//		组装sql
		String preval ="type,value,ip";
		String val ="?,?,?";
		if(name!=null&&!name.equals("")) {preval="name,"+preval;val="?,"+val;}
		if(target!=null&&!target.equals("")) {preval="target,"+preval;val="?,"+val;}
		String sql = "insert into liuyan1("+preval+") values("+val+")";
		
		
//		创建执行器 注入参数
		PreparedStatement pstm = null;
		try {
			System.out.println(sql);
			pstm = conn.prepareStatement(sql);
			int count = 1;
			if(target!=null&&!target.equals("")) {pstm.setString(count++, target);}
			if(name!=null&&!name.equals("")) {pstm.setString(count++, name);}
			pstm.setString(count++, type);
			pstm.setString(count++, value);
			pstm.setString(count++, ip);
//			获得修改结果
			int num = pstm.executeUpdate();
			return num == 1;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}



	
}
